import pymysql

db = None
cursor = None

def connect():
    # 如果db对象为空 则赋予新值 否则直接返回
    if(not db):
        db = pymysql.connect(
            host="localhost", #开发者后台,创建的数据库 “主机地址”
            port=3306, #开发者后台,创建的数据库 “端口”
            user='root',    #开发者后台,创建的数据库 “用户名”
            password='123456',     #开发者后台,创建的数据库 “初始密码”
            database ='airscriptdb', #开发者后台 ,创建的 "数据库"
            charset='utf8mb4'
        ) #连接数据库
    return db

# 执行完毕后记得关闭db,不然会并发连接失败哦
def close():
    # 如果db存在，则关闭数据库连接 并清除db对象
    if(db):
        db.close()
        db = None

# 可直接使用下方的方法 进行调用 无需connect 和 close
# 查询数据 count可选值：all one
def select(name:str, count:str = 'one', selectOptions:list[str] = [], where:dict = None):
    # 如果cursor游标不存在，则进行赋值
    if(not cursor and db):
        cursor = db.cursor()
    elif((not cursor and not db)or(cursor and not db)):
        db = connect()
        cursor = db.cursor()
    # 查询选项 sql字符串
    sqlOptionStr = ''
    if(selectOptions.__len__() != 0):
        sqlOptionStr = ','.join(selectOptions)
    # 查询条件 sql字符串
    sqlWhereStr = ''
    if(where):
        whereNamelist = where.keys()
        whereValueList = where.values()
        whereValues = ["'"+value+"'" if(type(value) == str) else '{0}'.format(value) for value in whereValueList]
        sqlWhereStr = ' and '.join(['{0} = {1}'.format(name,value) for name in whereNamelist for value in whereValues])

    if(sqlWhereStr == '' and sqlOptionStr == ''):
        #  *  stu  name = '张三'"
        sql = "SELECT * FROM {0}".format(name)
    elif(sqlWhereStr == '' and sqlOptionStr != ''):
        sql = "SELECT {0} FROM {1}".format(sqlOptionStr, name)
    elif(sqlWhereStr != '' and sqlOptionStr == ''):
        sql = "SELECT * FROM {0} WHERE {1}".format(name, sqlWhereStr)
    else:
        sql = "SELECT {0} FROM {1} WHERE {2}".format(sqlOptionStr, name, sqlWhereStr)
    cursor.execute(sql)
    res = None
    if(count == 'all'):
        res = cursor.fetchall()
    else:
        res = cursor.fetchone()
    # 执行完之后要记得关闭游标和数据库连接
    cursor.close()
    cursor = None
    close()
    return res

# 插入数据
def insert(name:str,obj:dict):
    # 如果cursor游标不存在，则进行赋值
    if(not cursor and db):
        cursor = db.cursor()
    elif((not cursor and not db)or(cursor and not db)):
        db = connect()
        cursor = db.cursor()
    namelist = obj.keys()
    valueList = obj.values()
    names = ','.join(namelist)
    values = ','.join(["'"+value+"'" if(type(value) == str) else '{0}'.format(value) for value in valueList])
    sql = "INSERT INTO {0} ( {1} ) VALUES ( {2} )".format(name,names,values)
    res = cursor.execute(sql)
    db.commit(); # 不要忘了提交,不然数据上不去哦
    # 执行完之后要记得关闭游标和数据库连接
    cursor.close()
    cursor = None
    close()
    return res

# 更新数据
def update(name:str,obj:dict, where:dict):
    # 如果cursor游标不存在，则进行赋值
    if(not cursor and db):
        cursor = db.cursor()
    elif((not cursor and not db)or(cursor and not db)):
        db = connect()
        cursor = db.cursor()
    namelist = obj.keys()
    valueList = obj.values()
    values = ["'"+value+"'" if(type(value) == str) else '{0}'.format(value) for value in valueList]
    sqlStr = ','.join(['{0} = {1}'.format(name,value) for name in namelist for value in values])
    
    if(not where):
        sql = "update {0} set {1}".format(name, sqlStr)
    else:
        whereNamelist = where.keys()
        whereValueList = where.values()
        whereValues = ["'"+value+"'" if(type(value) == str) else '{0}'.format(value) for value in whereValueList]
        sqlWhereStr = ' and '.join(['{0} = {1}'.format(name,value) for name in whereNamelist for value in whereValues])
        sql = "update {0} set {1} where {2}".format(name, sqlStr, sqlWhereStr)
    res = cursor.execute(sql)
    db.commit(); # 不要忘了提交,不然数据上不去哦
    # 执行完之后要记得关闭游标和数据库连接
    cursor.close()
    cursor = None
    close()
    return res

# 删除数据
def delete(name:str, where:dict, isDelete:bool=False):
    # 如果cursor游标不存在，则进行赋值
    if(not cursor and db):
        cursor = db.cursor()
    elif((not cursor and not db)or(cursor and not db)):
        db = connect()
        cursor = db.cursor()
    # 如果条件不存在 并且需要删除整表 则赋值sql删除整表语句
    if(not where and isDelete):
        sql = "delete from {0}".format(name)
    else:
        whereNamelist = where.keys()
        whereValueList = where.values()
        whereValues = ["'"+value+"'" if(type(value) == str) else '{0}'.format(value) for value in whereValueList]
        sqlWhereStr = ' and '.join(['{0} = {1}'.format(name,value) for name in whereNamelist for value in whereValues])
        sql = "delete from {0} where {1}".format(name, sqlWhereStr)
    res = cursor.execute(sql)
    db.commit(); # 不要忘了提交,不然数据上不去哦
    # 执行完之后要记得关闭游标和数据库连接
    cursor.close()
    cursor = None
    close()
    return res
